package sp.dao;

import lombok.extern.log4j.Log4j;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigInteger;
import java.sql.*;
import java.util.Date;
import java.util.*;

import static java.sql.Types.*;

// Dao的父类
@Log4j
public abstract class BaseDao<T> {
    static String driver = null;
    static String url = null;
    static String username = null;
    static String password = null;
    static {
        Properties prop = new Properties();
        try(InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties")){
            prop.load(in); // 加载属性文件
            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");
            Class.forName(driver); // 加载驱动程序
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    static ThreadLocal<Connection> threadLocal = new ThreadLocal<>(); // 线程本地存储
    public static Connection getConnection() throws SQLException {
        if(threadLocal.get()==null) {
            Connection connection = DriverManager.getConnection(url, username, password);
            threadLocal.set(connection);
        }
        return threadLocal.get();
    }
    public static void close(ResultSet rs, Statement st,Connection conn){
        try {
            if (rs != null) rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        try {
            if (st != null) st.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        try {
            if (conn != null) conn.close();
            threadLocal.set(null); // 重置线程本地存储
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public Integer executeUpdate(String sql,Object[] params){
        try(PreparedStatement ps = getConnection().prepareStatement(sql);){
            if(params != null){
                for(int i=0;i<params.length;i++){ // 逐个绑定参数
                    ps.setObject(i+1,params[i]);
                }
            }
            ps.executeUpdate();  // 执行SQL语句
            if(sql.startsWith("insert")){
                Integer id = getNewId(); // 获取刚刚添加的记录的自增id
                return id;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return -1;
    }
    public Integer getNewId() throws SQLException {// 查询新增id的
//        Integer id = (Integer)findById("select last_insert_id() as id",null);
        try(
                Statement statement =  getConnection().createStatement();
                ResultSet set =  statement.executeQuery("select last_insert_id() as id")
                ){
            set.next();
            return set.getInt("id");
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;


    }
    public List<T> query(String sql,Object []params) {
        List<T> list = new ArrayList<>();
        ResultSet rs = null;
        try(PreparedStatement ps = getConnection().prepareStatement(sql);){
            if(params != null){
                for(int i=0;i<params.length;i++){
                    ps.setObject(i+1,params[i]);
                }
            }
            log.trace(sql+",参数：" + Arrays.toString(params));
            rs = ps.executeQuery();
            while(rs.next()){
                list.add(readEntity(rs));
            }
            log.trace("查找了" + list.size() + "条记录！");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,null,null);  // 关闭结果集
        }
        return list;
    }
    public T readEntity(ResultSet rs) throws Exception {
        ParameterizedType parameterizedType =
                (ParameterizedType) this.getClass().getGenericSuperclass();
        Type[] ar = parameterizedType.getActualTypeArguments();
        if(ar.length == 1){
            return readEntityDefault(rs, (Class<T>) ar[0]);
        }
        return null;
    }
    public T readEntityDefault(ResultSet rs,Class<T> cl) throws Exception{
        T entity = cl.newInstance();
        ResultSetMetaData rsmd = rs.getMetaData(); // 获取元信息对象
        int columnsCount = rsmd.getColumnCount();  // 获取结果集中的列数
        for(int i=1;i<=columnsCount;i++){
            String columnName = rsmd.getColumnName(i);      // 列名
            String label = rsmd.getColumnLabel(i);          // 别名
            if(label!=null){
                columnName = label;
            }
            String fieldName = columnToField(columnName);   // 属性名
            Field field = null;
            try {
                field = cl.getDeclaredField(fieldName);
                field.setAccessible(true);                 // 开启访问权限
            }catch (NoSuchFieldException e){
                e.printStackTrace();
            }
            StringBuilder bd = new StringBuilder();
            int column = rsmd.getColumnType(i);
            switch (column){
                case INTEGER:
                case TINYINT:
                case SMALLINT:
                    Integer intVal = rs.getInt(i);
                    field.set(entity,intVal);
                    break;
                case CHAR:
                case VARCHAR:
                    String strValue = rs.getString(i);
                    field.set(entity,strValue);
                    break;
                case TIMESTAMP:
                case TIME:
                case DATE:
                    Date dateVal = rs.getDate(i);
                    field.set(entity,dateVal);
                    break;
                default:
                    Object objVal = rs.getObject(i);
                    if(objVal instanceof BigInteger)
                        objVal = ((BigInteger)objVal).intValue();
                    field.set(entity,objVal);
            }
        }
        return entity;
    }
    protected String columnToField(String columnName){
        StringBuilder bd = new StringBuilder();
        if(columnName.indexOf("_")>0){
            for(int pos=columnName.indexOf("_"),s=0;
                pos<columnName.length() && s<columnName.length();){
                if(pos!=-1) {
                    bd.append(columnName.substring(s, pos));
                }else{
                    bd.append(columnName.substring(s));
                    break;
                }
                pos++;
                if(pos>columnName.length()) break;
                char ch = columnName.charAt(pos);
                if(ch >= 'a' && ch <= 'z') {
                    ch -= ('a' - 'A');
                }
                bd.append(ch);
                s = pos + 1;
                pos = columnName.indexOf('_',s);
            }
        }else{
            return columnName;
        }
        return bd.toString();
    }
    public T findById(String sql,Object[] params){
        List<T> list = query(sql,params);
        if(list.size()>0) return list.get(0);
        return null;
    }
    public Page<T> queryByPage(String countSql, String sql, Object []params, int page, int rows){
        List<T> list = new ArrayList<>();
        int total;
        Page<T> pb = null;
        // 计算总记录数
        ResultSet countRs = null;
        ResultSet rs = null;
        try(
                PreparedStatement countPs = getConnection().prepareStatement(countSql);
                PreparedStatement ps = getConnection().prepareStatement(sql);
        ){
            if(params != null){
                for(int i=0;i<params.length;i++){
                    countPs.setObject(i+1,params[i]);
                }
            }
            countRs = countPs.executeQuery();
            countRs.next();
            total = countRs.getInt(1); // 获取总记录数
            pb = new Page<>(page,rows,total); // 计算分页参数
            if(params != null){
                for(int i=0;i<params.length;i++){
                    ps.setObject(i+1,params[i]);
                }
            }
            // 绑定分页参数  select * from st where name=? limit ?,?
            int start = params == null ? 1 : params.length + 1;
            ps.setInt(start,pb.getStart());
            ps.setInt(start+1,rows);
            rs = ps.executeQuery();   // 查询一页数据
            while(rs.next()){
                list.add(readEntity(rs));
            }
            pb.setList(list);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(countRs,null,null);
            close(rs,null,null);
        }
        return pb;
    }
    // 开启事务
    public static boolean begin() throws SQLException {
        boolean oldAutoCommit = getConnection().getAutoCommit(); // 获取连接原来的提交状态
        getConnection().setAutoCommit(false);                    // 关闭连接的自动提交
        log.trace("开启事务");
        return oldAutoCommit;
    }
    // 提交事务
    public static void commit(boolean oldAutoCommit) throws SQLException {
        getConnection().commit();                       // 提交事务
        getConnection().setAutoCommit(oldAutoCommit);   // 还原状态
        log.trace("提交事务");
    }
    // 回滚事务
    public static void rollback(boolean oldAutoCommit) throws SQLException {
        getConnection().rollback();                     // 回滚事务
        getConnection().setAutoCommit(oldAutoCommit);   // 还原状态
        log.trace("回滚事务");
    }
}
